package main

import (
	"log"
	"github.com/go-xorm/xorm"
	_ "github.com/go-sql-driver/mysql"
	"fmt"
	"time"
)

const MasterDSN  = "root:1@tcp(127.0.0.1:3306)/test_tool?charset=utf8&sslmode=disable"

/*
CREATE TABLE `user_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '中文名',
  `sys_created` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
  `sys_updated` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '最后修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
*/
type UserInfo struct {
	Id         int      `xorm:"not null pk autoincr"`
	Name       string
	SysCreated int
	SysUpdated int
}

var engine *xorm.Engine

func main() {
	engine = newEngin()
	// execute()
	// ormInsert()
	query()
	ormGet()
	ormGetCols()
	ormCount()
	// ormFindRows()
	//ormUpdate()
	//ormOmitUpdate()
	//ormMustColsUpdate()
}

// 连接db
func newEngin() *xorm.Engine {
	engine,err := xorm.NewEngine(DriverName,MasterDSN)
	if err != nil {
		log.Fatal(newEngin,err)
		return nil
	}
	engine.ShowSQL(true)
	return engine
}
// query
func query() {
	sql:= "select * from user_info"
	// rs,err := engine.Query(sql)
	// rs,err := engine.QueryInterface(sql)
	rs,err := engine.QueryString(sql)
	if err != nil {
		log.Fatal("query",sql, err)
		return
	}
	total := len(rs)
	if total == 0 {
		fmt.Println("没有任何数据",sql)
	}else{
		for i,data := range rs {
			fmt.Printf("%d =  %v\n",i,data)
		}
	}
	// time.Time
}


func execute() {
	sql := "insert into user_info values(null,'name',0,0)"
	affected,err := engine.Exec(sql)
	if err != nil {
		log.Fatal("execute error",err)
	}else{
		id,_ := affected.LastInsertId()
		rows,_ := affected.RowsAffected()
		fmt.Println("execute id=",id,", rows",rows)
	}
}

func ormInsert() {
	UserInfo := &UserInfo{
		Id:         0,
		Name:       "梅西",
		SysCreated: 0,
		SysUpdated: 0,
	}
	id,err := engine.Insert(UserInfo)
	if err != nil {
		log.Fatal("ormInsert error",err)
	}else{
		fmt.Println("ormInser id=",id)
		fmt.Printf("%v\n",*UserInfo)
	}
}
func ormGet() {
	UserInfo := &UserInfo{Id:2}
	ok,err := engine.Get(UserInfo)
	if ok {
		fmt.Printf("%v\n",*UserInfo)
	}else if err != nil{
		log.Fatal("ormget error",err)
	}else{
		fmt.Println("ormGet empty id=",UserInfo.Id)
	}
}
func ormGetCols() {
	UserInfo := &UserInfo{Id:2}
	ok,err := engine.Cols("name").Get(UserInfo)
	if ok {
		fmt.Printf("ormGetCols",err)
	}else{
		fmt.Println("ormGetCols empty id=2")
	}
}

func ormCount() {
	// count,err := engine.Count(&UserInfo{})
	// count,err := engine.Where("name_zh=?","梅西").Count(&UserInfo{})
	count,err := engine.Count(&UserInfo{Name:"梅西"})
	if err == nil {
		fmt.Printf("count=%v\n",count)
	}else{
		log.Fatal("ormCount error",err)
	}
}
//find
func ormFindRows() {
	list := make([]UserInfo,0)
	// list := make(map[int]UserInfo)
	// err := engine.Find(&list)
	// err := engine.Where("id>?",1).Limit(100,0).Find(&list)
	err := engine.Cols("id","name").Where("id>?",0).
		Limit(10).Asc("id","sys_updated").Find(&list)

	// list := make([]map[string]string,0)
	// err := engine.Table("star_info").Cols("id","name_zh","name_en").Where("id>?",1).Find(&list)

	if err == nil {
		fmt.Printf("%v\n",list)
	}else{
		log.Fatal("ormFindRows",err)
	}
}
// 更新一个数据
func ormUpdate() {
	// 全部更新
	// UserInfo := &UserInfo{NameZh:"厕所名"}
	// ok,err :=  engine.Update(UserInfo)
	// 根据Id更新
	UserInfo := &UserInfo{Name:"梅西"}
	ok,err := engine.ID(2).Update(UserInfo)
	fmt.Println(ok,err)

}

// 排除某字段
func ormOmitUpdate() {
	info := &UserInfo{Id:1}
	ok,_ := engine.Get(info)
	if ok {
		if info.SysCreated >0 {
			ok,_ := engine.ID(info.Id).
				Omit("sys_created").
				Update(&UserInfo{SysCreated:0,
				SysUpdated:int(time.Now().Unix())})
			fmt.Printf("ormOmitUpdate,rows=%d, " + "sys_created=%d\n",ok,0)
		}else {
			ok,_ := engine.ID(info.Id).
				Omit("sys_created").
				Update(&UserInfo{SysCreated:1,SysUpdated:int(time.Now().Unix())})
			fmt.Printf("ormOmitUpdate,rows=%d, " + "sys_created=%d\n",ok,0)
		}
	}
}
// 字段为空也可以更新 (0,"",..)
func ormMustColsUpdate() {
	info := &UserInfo{Id:1}
	ok,_ := engine.Get(info)
	if ok {
		if info.SysCreated >0 {
			ok,_ := engine.ID(info.Id).
				MustCols("sys_created").
				Update(&UserInfo{SysCreated:0,
				SysUpdated:int(time.Now().Unix())})
			fmt.Printf("ormMustColsUpdate,rows=%d, " + "sys_created=%d\n",ok,0)
		}else {
			ok,_ := engine.ID(info.Id).
				MustCols("sys_created").
				Update(&UserInfo{SysCreated:1,SysUpdated:int(time.Now().Unix())})
			fmt.Printf("ormMustColsUpdate,rows=%d, " + "sys_created=%d\n",ok,0)
		}
	}
}
const DriverName = "mysql"
